﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using FirebirdSql.Data.FirebirdClient;
using RMSM;

namespace AC
{
	public static class DBUtil
	{
		public static bool ExistColumn(string tableName, string colName)
		{
			bool hasColumn = false;

			using (FbConnection cnn = new FbConnection(Session.CnnStr))
			{
				cnn.Open();

				string scmd = "SELECT rf.rdb$field_name AS COLNAME FROM rdb$fields f JOIN rdb$relation_fields rf ON rf.rdb$field_source = f.rdb$field_name WHERE rf.rdb$relation_name = '" + tableName + "' ORDER BY rf.rdb$field_position;";
				using (FbCommand cmdTable = new FbCommand(scmd, cnn))
				{
					using (FbDataReader readerTable = cmdTable.ExecuteReader())
					{
						colName = colName.ToUpper();
						string s = "";

						while (readerTable.Read())
						{
							s = ((string)readerTable["COLNAME"]).Trim().ToUpper();
							if (colName == s)
							{
								hasColumn = true;
								break;
							}
						}
					}
				}
			}

			return hasColumn;
		}

		public static string ExecNonQuery(string scmd)
		{
			if (string.IsNullOrEmpty(scmd))
				return null;

			try
			{
				using (FbConnection cnn = new FbConnection(Session.CnnStr))
				{
					cnn.Open();

					using (FbCommand cmd = new FbCommand(scmd, cnn))
					{
						cmd.ExecuteNonQuery();
					}
				}
			}
			catch (Exception ex)
			{
				return ex.Message;
			}

			return null;
		}

		public static string AddColumn(string tableName, string colName, string FbDataType, bool AllowNull, string Description)
		{
			string s = colName.ToUpper() + " " + FbDataType;
			if (AllowNull == false)
				s += " NOT NULL";

			string scmd = "ALTER TABLE " + tableName + " ADD " + s + ";";
			string serror = ExecNonQuery(scmd);
			if (serror != null)
				return serror;

			string fullDesc = "{" + colName + "}";
			if (string.IsNullOrEmpty(Description) == false)
				fullDesc += Description;

			scmd = "COMMENT ON COLUMN " + tableName + "." + colName.ToUpper() + " is '" + fullDesc + "';";
			serror = ExecNonQuery(scmd);
			return serror;
		}
	}
}
